﻿using System.Collections.Generic;
using System.Data;
using Utils.DB.OleDb;

namespace KQReport
{
    public class User
    {
        public User(IDataReader reader, bool small)
        {
            Id = reader.GetInt32(0);
            Num = reader.GetString(1);
            Name = reader.GetString(2);
            Sex = reader.GetString(3);
            Sn = reader.GetString(4);
            Card = reader.GetString(5);
            Depart = reader.GetString(6);
        }

        public int Id { get; set; }

        public string Num { get; set; }
        public string Name { get; set; }
        public string Sex { get; set; }
        public string Sn { get; set; }
        public string Card { get; set; }
        public string Depart { get; set; }

        public static Dictionary<string,string> LoadAllSmallUsers()
        {
            var reader = SqlHelperEx.ExecuteReader("select Badgenumber,Name from USERINFO");
            var users = new Dictionary<string, string>();
            while (reader.Read())
                users.Add(reader.GetString(0), reader.GetString(1));
            return users;
        }
        public static List<User> LoadUsers(int departId, string user)
        {
            var basesql =
                @"SELECT USERINFO.UserId as Id,USERINFO.Badgenumber AS Num, USERINFO.Name,
                    iif(isnull(USERINFO.Gender),'',USERINFO.Gender) AS Sex, 
                USERINFO.street AS Card, USERINFO.SSN AS Sn,DEPARTMENTS.DEPTNAME AS Depart
FROM      ((USERINFO INNER JOIN
                DEPARTMENTS ON USERINFO.DEFAULTDEPTID = DEPARTMENTS.DEPTID) INNER JOIN
                DEPARTMENTS DEPARTMENTS_1 ON DEPARTMENTS.SUPDEPTID = DEPARTMENTS_1.DEPTID)
WHERE   (DEPARTMENTS.DEPTID = {0} OR DEPARTMENTS.SUPDEPTID = {0} OR DEPARTMENTS_1.SUPDEPTID = {0})";

            // AND USERINFO.Name in
            var sql = string.Format(basesql, departId);
            if (!string.IsNullOrEmpty(user))
                sql += string.Format(" AND USERINFO.Badgenumber in ({0})", user);
            var reader = SqlHelperEx.ExecuteReader(sql);
            var users = new List<User>();
            while (reader.Read())
                users.Add(new User(reader, false));
            return users;
        }
        public static DataTable LoadUsersTable(int departId, string user)
        {
            var basesql =
                @"SELECT USERINFO.UserId as Id,USERINFO.Badgenumber AS Num,USERINFO.Name, USERINFO.Gender AS Sex, 
                USERINFO.street AS Card, USERINFO.SSN AS Sn,DEPARTMENTS.DEPTNAME AS Depart
FROM      ((USERINFO INNER JOIN
                DEPARTMENTS ON USERINFO.DEFAULTDEPTID = DEPARTMENTS.DEPTID) INNER JOIN
                DEPARTMENTS DEPARTMENTS_1 ON DEPARTMENTS.SUPDEPTID = DEPARTMENTS_1.DEPTID)
WHERE   (DEPARTMENTS.DEPTID = {0} OR DEPARTMENTS.SUPDEPTID = {0} OR DEPARTMENTS_1.SUPDEPTID = {0})";

            // AND USERINFO.Name in
            var sql = string.Format(basesql, departId);
            if (!string.IsNullOrEmpty(user))
                sql += string.Format(" AND USERINFO.Badgenumber in ({0})", user);
            return SqlHelperEx.ExecuteDataTable(sql);
        }
    }
}